﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spBkp_Log]
#-- Purpose:		Performs a log backup of a database.  Chooses between
#--					LiteSpeed if available, otherwise standard Backup
#--	Last Update:	07/14/2016
#--					For a complete history - please review comments in Version
#--					Control.
#-- Optional XPs:	[master].[dbo].[xp_backup_log]
#-- Called By:		[dbo].[spBkp_LogAll]
#--					Restore Database Utility Executable
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spBkp_Log]
(
	@database_name	sysname,
	@verify			bit					= 1,
	@label			nvarchar(128)		= NULL,
	@ignore_skip	bit					= 0
)
WITH EXECUTE AS OWNER
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE		@dir varchar(8000), @sCommand varchar(8000), @file_name varchar(8000),
			@noarch bit, @readonly bit, @offline bit, @is_standby bit,
			@current_date datetime, @job_name_filter varchar(125),
			@hadr_enabled bit, @preferred_replica bit, @major int
DECLARE		@progname sysname
DECLARE @tPreferredReplica TABLE ([yesno] bit)
DECLARE @sSQL varchar(MAX)

--- Define the name of this program (for logs)
SELECT		@progname = 'spBkp_Log'

--- Ensure the database skips backup if configured not to backup
IF ISNULL(@ignore_skip, 0) = 0 
	AND EXISTS (SELECT TOP 1 database_name FROM [dbo].[vwMaint_DoNotBackup] WHERE database_name = @database_name)
  BEGIN
	RETURN
  END

--- @database_name cannot be tempdb
SELECT	@database_name = LTRIM(RTRIM(ISNULL(@database_name, '')))
IF @database_name = 'tempdb'
  BEGIN
	PRINT 'tempdb database cannot be backed up (' + @database_name + ')'
	RETURN
  END

--- @database_name is a mandatory fields
IF @database_name = ''
  BEGIN
	RAISERROR ('The name of the database to backup is required (@database_name)', 16, 1)
	RETURN
  END

--- @database_name must be an existing database
IF NOT EXISTS (SELECT TOP 1 name FROM [master].[sys].[databases] WHERE name = @database_name)
  BEGIN
	RAISERROR ('The requested database does not exist', 16, 1)
	RETURN
  END

--- Snapshot databases cannot be backed up
IF EXISTS (SELECT TOP 1 name FROM [master].[sys].[databases] WHERE name = @database_name AND source_database_id IS NOT NULL)
  BEGIN
	PRINT 'Snapshot databases cannot be backed up (' + @database_name + ')'
	RETURN
  END

BEGIN TRY
	--- Get Current date/time stamp and set base job name
	SELECT	@current_date = GETDATE(),
			@job_name_filter = 'DBA: Backup Log',
			@major = CAST(PARSENAME(CONVERT(varchar,SERVERPROPERTY('ProductVersion')),4) AS int)
	
	--- Ensure the directory exists  (xp)
	EXEC	[dbo].[spBkp_GetDirectory] @database_name = @database_name, @backup_directory_name = @dir OUTPUT 
	EXEC	[dbo].[spIO_CreateDirectory] @dir

	--- Get File Name and database settings
	EXEC	[dbo].[spBkp_GetLogBackupName] @database_name = @database_name, @backup_directory_name = @file_name OUTPUT
	SELECT	@noarch = [dbo].[fnConfig_IsDatabaseNoArchive] ( @database_name ),
			@offline = ISNULL( [dbo].[fnConfig_IsDatabaseOffline] ( @database_name ), 0 ),
			@readonly = ISNULL( [dbo].[fnConfig_IsDatabaseReadOnly] ( @database_name ), 0 ),
			@is_standby = ISNULL( [dbo].[fnConfig_IsDatabaseStandby] ( @database_name ), 0 ),
			@hadr_enabled = ISNULL(CAST(SERVERPROPERTY('IsHadrEnabled') as bit), 0),
			@preferred_replica = 1

	--- Update the readonly flag if this is a HADR database (SQL 2012+)
	IF @readonly = 1 AND @major > 10
	  BEGIN
		--- Is this the preferred backup location
		IF OBJECT_ID('[sys].[dm_hadr_availability_group_states]') IS NOT NULL
		  BEGIN
			SELECT	@sSQL = REPLACE('SELECT preferred_replica = [sys].[fn_hadr_backup_is_preferred_replica](''[VAR_DBNAME]'')', '[VAR_DBNAME]', @database_name)
			INSERT INTO @tPreferredReplica EXEC(@sSQL)
			SELECT @preferred_replica = [yesno] FROM @tPreferredReplica
		  END

		IF @@rowcount > 0
			SET @readonly = 0
	  END

	--- Build the command to backup the database
	---		Determine the recovery model supports Trans Logs
	IF @noarch = 0 AND @readonly = 0 AND @offline = 0 AND @is_standby = 0 AND @database_name <> 'tempdb' AND (
			@hadr_enabled = 0
			OR (@hadr_enabled = 1 AND @preferred_replica = 1)
		)
	  BEGIN
		IF EXISTS(select * from [master].[dbo].[sysobjects] where id = object_id(N'[master].[dbo].[xp_backup_log]'))
		  BEGIN
			SELECT	@sCommand = 'EXEC [master].[dbo].[xp_backup_log] @database = ''' + @database_name + ''', @filename = ''' + @file_name + ''''

			IF @label IS NOT NULL
				SELECT	@sCommand = @sCommand + ', @desc=''' + @label + ''''
		  END
		ELSE
		  BEGIN
			--- Base Command
			SELECT	@sCommand = 'BACKUP LOG [' + @database_name + '] TO DISK = ''' + @file_name + ''''

			--- Set the backup label
			IF @label IS NOT NULL
			  BEGIN
				SELECT	@sCommand = @sCommand + ' WITH NAME = ''' + @label + ''''

				IF @verify = 1
				  BEGIN
					SELECT @sCommand = @sCommand + ', CHECKSUM; RESTORE VERIFYONLY FROM DISK = ''' + @file_name + ''''
				  END
			  END
			ELSE IF @verify = 1
			  BEGIN
				SELECT @sCommand = @sCommand + ' WITH CHECKSUM; RESTORE VERIFYONLY FROM DISK = ''' + @file_name + ''''
			  END
		  END

		--- Execute the command (backup the database)
		IF LEN(@sCommand) > 0
		  BEGIN
			EXEC	(@sCommand)
			EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sCommand
		  END
	  END
END TRY
BEGIN CATCH
	--- Log the error - if possible
	DECLARE @sResults varchar(MAX)
	SELECT @sResults = 'The database [' + @database_name + '] failed to backup (' + ISNULL(@sCommand, 'Unknown') + ').'

	IF ERROR_NUMBER() IS NOT NULL
	  BEGIN
		SELECT @sResults = @sResults + CHAR(13)
						+ 'ErrNo: ' + ISNULL(CAST(ERROR_NUMBER() as varchar(50)), '') + CHAR(13)
						+ 'ErrMsg: ' + ISNULL(ERROR_MESSAGE(), '') + CHAR(13)
						+ 'Proc: ' + ISNULL(ERROR_PROCEDURE(), '') + CHAR(13)
						+ 'LineNo: ' + ISNULL(CAST(ERROR_LINE() as varchar(10)), '')

		--- Print the error message to sysout
		PRINT @sResults

		--- Try a differential backup
		IF ERROR_NUMBER() IN (4214, 3013)
		  BEGIN
			BEGIN TRY
				--- Log that a differential is being auto-run
				SELECT	@sResults = 'The database [' + ISNULL(@database_name, '') + '] failed to backup the log - likely due to recovery path issues.  Starting a differential backup.'
				EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sResults

				EXEC [dbo].[spBkp_Differential]  @database_name = @database_name, @verify = @verify, @label = @label
				EXEC [dbo].[spBkp_Log]  @database_name = @database_name, @verify = @verify, @label = @label

				--- Must raise an error so sqlcmd catches the severity level
				SELECT	@sResults = 'Auto-differential backup complete.  View logs if there were errors.'
				EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sResults
				RETURN
			END TRY
			BEGIN CATCH
				SELECT	@sResults = @sResults + ' - spBkp_Differential attempted'
			END CATCH
		  END
	  END

	--- Insert the error in the database
	IF @sResults IS NOT NULL
	  BEGIN
		INSERT INTO [dbo].[tblMaint_Log] (mnt_table_catalog, mnt_action_date, mnt_action_type, mnt_success, mnt_results)
			VALUES (@database_name, GETDATE(), 'backup_log', 0, @sResults)

		--- Must raise an error so sqlcmd catches the severity level
		RAISERROR (@sResults, 16, 1)
		RETURN
	  END
	ELSE
	  BEGIN
		--- Must raise an error so sqlcmd catches the severity level
		SELECT @sResults = '[' + @progname + '] The database [' + ISNULL(@database_name, '') + '] failed to backup (' + ISNULL(@sCommand, '') + ').'
		RAISERROR (@sResults, 16, 1) WITH LOG
		RETURN
	  END
END CATCH

SET NOCOUNT OFF
